﻿using KWT.DRG_DIP.DB;
using KWT.DRG_DIP.DTO.Statistic;
using KWT.DRG_DIP.DTO.Statistic.Group;
using KWT.DRG_DIP.DTO.Statistic.Overview;
using KWT.DRG_DIP.ISvc;
using KWT.DRG_DIP.PO.Basic;
using KWT.DRG_DIP.PO.Settlement;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace KWT.DRG_DIP.Svc
{
    public class DIP_StatisticSvc : IStatisticSvc
    {
        public EFContext ef { get; set; }
        public DapperHelper dapper { get; set; }
        public DTO.Statistic.Overview.OutPart1 Cacl(InOverview dto)
        {
            var paras = new { dto.StartDate, dto.EndDate, dto.AreaID };
            string sql1 = @"SELECT sum(b.Score) zqz FROM sys_list a inner join sys_dip_para b
on a.Group_Code =b.Disease_Code where SettleDate>=@StartDate and SettleDate<=@EndDate and AreaID=@AreaID";
            decimal zqz = Convert.ToDecimal(dapper.ExecuteScalar(sql1, paras));

            int bls = ef.Set<Sys_List>().Count(x => x.Group_Code != "" && x.Group_Code != "0000" && x.AreaID == dto.AreaID && x.SettleDate >= Convert.ToDateTime(dto.StartDate) && x.SettleDate <= Convert.ToDateTime(dto.EndDate));
            int zbls = ef.Set<Sys_List>().Count(x => x.SettleDate >= Convert.ToDateTime(dto.StartDate) && x.SettleDate <= Convert.ToDateTime(dto.EndDate) && x.AreaID == dto.AreaID);
            decimal bls_per = Math.Round((decimal)bls / (decimal)zbls * 100, 2);

            string sql2 = @"SELECT sum(b.Total) total FROM sys_list a inner join list_chargeinfo b
on a.ListID =b.ListID  where a.Group_Code!='' and a.Group_Code!='0000' and SettleDate>=@StartDate and SettleDate<=@EndDate and AreaID=@AreaID";
            decimal zfy = Convert.ToDecimal(dapper.ExecuteScalar(sql2, paras));
            decimal z_zfy = ef.Set<List_ChargeInfo>().Sum(x => x.Total);
            decimal zfy_per = Math.Round((zfy / z_zfy), 4) * 100;

            string sql3 = @"SELECT sum(b.Remain) remain FROM sys_list a inner join list_chargeinfo b
on a.ListID =b.ListID  where SettleDate>=@StartDate and SettleDate<=@EndDate and AreaID=@AreaID";
            decimal zfc = Convert.ToDecimal(dapper.ExecuteScalar(sql3, paras));

            string sql_bfb = @"select avg(1-(Remain/Total)) from sys_list a 
inner join list_basicinfo b on a.ListID=b.ListID
inner join list_chargeinfo c on c.ListID=a.ListID
inner join sys_dip_para d on d.DRG_Code=a.Disease_Code where SettleDate>=@StartDate and SettleDate<=@EndDate and AreaID=@AreaID";
            decimal bfb = Math.Round(Convert.ToDecimal(dapper.ExecuteScalar(sql_bfb, paras)) * 100, 2);

            int other_bls = ef.Set<Sys_List>().Count(x => new string[] { "", "0000" }.Contains(x.Group_Code) && x.SettleDate >= Convert.ToDateTime(dto.StartDate) && x.SettleDate <= Convert.ToDateTime(dto.EndDate) && x.AreaID == dto.AreaID);
            decimal other_bls_per = Math.Round(((decimal)other_bls / (decimal)zbls), 2) * 100;

            string sql4 = @"SELECT sum(b.Total) total FROM sys_list a inner join list_chargeinfo b
on a.ListID =b.ListID  where a.Group_Code in('','0000') and SettleDate>=@StartDate and SettleDate<=@EndDate and AreaID=@AreaID";
            decimal other_zfy = Convert.ToDecimal(dapper.ExecuteScalar(sql4, paras));
            decimal other_zfy_per = Math.Round((other_zfy / z_zfy), 2) * 100;
            var outPart1 = new DTO.Statistic.Overview.OutPart1()
            {
                zqz = zqz,
                bls = bls,
                bls_per = bls_per,
                zfy = zfy,
                zfy_per = zfy_per,
                zfc = zfc,
                bfb = bfb,
                other_bls = other_bls,
                other_bls_per = other_bls_per,
                other_zfy = other_zfy,
                other_zfy_per = other_zfy_per,
            };
            return outPart1;
        }
        public (List<DTO.Statistic.Category.OutPageList> list, int total) CategoryPageList(DTO.Statistic.Category.InPageList dto)
        {
            string sql = @"select CategoryName name,CategoryID code,sum(total) total,count(a.ListID) cnt,sum(Remain) remain,sum(score) zqz,round(sum(Total)/count(distinct a.ListID),2) cjfy,round(sum(Remain)/count(distinct a.ListID),2) cjjy from sys_list a 
left join list_chargeinfo b on a.ListID=b.ListID
inner join sys_dip_para c on a.Group_Code=c.Disease_Code
where SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql += " and CategoryID=@CategoryID ";
            }
            sql += " group by CategoryName,CategoryID  ";
            if (dto.Type == 1)
            {
                sql += " having remain>=0 ";
            }
            if (dto.Type == -1)
            {
                sql += " having remain<0 ";
            }
            if (!string.IsNullOrEmpty(dto.Col) && !string.IsNullOrEmpty(dto.Sort))
            {
                if (dto.Sort.Equals("normal"))
                {
                    sql += "order by @Col";
                }
                else
                {
                    sql += "order by @Col @Sort";
                }
            }
            sql += "limit @PageSize OFFSET @PageNo";

            var list = dapper.Select<DTO.Statistic.Category.OutPageList>(sql, new { dto.CategoryID, dto.StartDate, dto.EndDate, dto.Col, dto.Sort, dto.AreaID, dto.PageSize, PageNo = (dto.PageNo - 1) * dto.PageSize });

            string sql1 = @"select count(1) from (
select count(1) cnt,CategoryName,CategoryID from sys_list a 
left join list_chargeinfo b on a.ListID=b.ListID
inner join sys_dip_para c on a.Group_Code=c.Disease_Code
where SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql1 += " and CategoryID=@CategoryID ";
            }
            sql1 += " group by CategoryName,CategoryID ";
            if (dto.Type == 1)
            {
                sql1 += " having sum(Remain)>=0) a ";
            }
            if (dto.Type == -1)
            {
                sql1 += " having sum(Remain)<0) a ";
            }
            if (dto.Type == 0)
            {
                sql1 += " ) a ";
            }
            int total = Convert.ToInt32(dapper.ExecuteScalar(sql1, new { dto.StartDate, dto.EndDate, dto.AreaID }));

            string sql_jc = @"select CategoryID code,sum(Amount) jcf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
inner join list_chargeinfo c on c.ListID=a.ListID
inner join sys_dip_para d on a.Group_Code=d.Disease_Code
where b.ItemName='检查费' and SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID
group by CategoryName,CategoryID";
            if (dto.Type == 1)
            {
                sql_jc += " having sum(Remain)>=0 ";
            }
            if (dto.Type == -1)
            {
                sql_jc += " having sum(Remain)<0 ";
            }
            var jc = dapper.Select<dynamic>(sql_jc, new { dto.StartDate, dto.EndDate, dto.AreaID });

            string sql_jy = @"select CategoryID code,sum(Amount) jcf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
inner join list_chargeinfo c on c.ListID=a.ListID
inner join sys_dip_para d on a.Group_Code=d.Disease_Code
where b.ItemName='化验费' and SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID
group by CategoryName,CategoryID";
            if (dto.Type == 1)
            {
                sql_jy += " having sum(Remain)>=0 ";
            }
            if (dto.Type == -1)
            {
                sql_jy += " having sum(Remain)<0 ";
            }
            var jy = dapper.Select<dynamic>(sql_jy, new { dto.StartDate, dto.EndDate, dto.AreaID });

            string sql_yf = @"select CategoryID code,sum(Amount) jcf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
inner join list_chargeinfo c on c.ListID=a.ListID
inner join sys_dip_para d on a.Group_Code=d.Disease_Code
where  b.ItemName in ('西药费','中药饮片费','中成药费') and SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID
group by CategoryName,CategoryID";
            if (dto.Type == 1)
            {
                sql_yf += " having sum(Remain)>=0 ";
            }
            if (dto.Type == -1)
            {
                sql_yf += " having sum(Remain)<0 ";
            }
            var yf = dapper.Select<dynamic>(sql_yf, new { dto.StartDate, dto.EndDate, dto.AreaID });

            string sql_hcf = @"select CategoryID code,sum(Amount) jcf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
inner join list_chargeinfo c on c.ListID=a.ListID
inner join sys_dip_para d on a.Group_Code=d.Disease_Code
where b.ItemName='卫生材料费' and SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID
group by CategoryName,CategoryID";
            if (dto.Type == 1)
            {
                sql_hcf += " having sum(Remain)>=0 ";
            }
            if (dto.Type == -1)
            {
                sql_hcf += " having sum(Remain)<0 ";
            }
            var hcf = dapper.Select<dynamic>(sql_hcf, new { dto.StartDate, dto.EndDate, dto.AreaID });


            string sql_cz_dc = @"select  code,min(remain) czje,CONCAT(DcName,' - ',DcCode) doc_name from(
select CategoryID code,sum(remain) remain,c.DcName,c.DcCode from sys_list a 
inner join list_chargeinfo b on a.ListID=b.ListID
inner join list_personinfo c on a.ListID=c.ListID
inner join sys_dip_para d on a.Group_Code=d.Disease_Code
where SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID
group by CategoryID,c.DcName,c.DcCode ";
            if (dto.Type == 1)
            {
                sql_cz_dc += " having sum(Remain)>=0) a ";
            }
            if (dto.Type == -1)
            {
                sql_cz_dc += " having sum(Remain)<0) a ";
            }
            if (dto.Type == 0)
            {
                sql_cz_dc += " ) a ";
            }
            sql_cz_dc += " group by code, CONCAT(DcName, ' - ', DcCode)";
            var cz_dc = dapper.Select<dynamic>(sql_cz_dc, new { dto.StartDate, dto.EndDate, dto.AreaID });


            string sql_cz_dss_grp = @"select  code,min(remain) czje,grp_code,grp_name from(
select CategoryID code,Group_Code grp_code,Group_Name grp_name,sum(remain) remain from sys_list a 
inner join list_chargeinfo b on a.ListID=b.ListID
inner join sys_dip_para c on a.Group_Code=c.Disease_Code
where SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID
group by CategoryID,Group_Code,Group_Name ";
            if (dto.Type == 1)
            {
                sql_cz_dss_grp += " having sum(Remain)>=0) a ";
            }
            if (dto.Type == -1)
            {
                sql_cz_dss_grp += " having sum(Remain)<0) a ";
            }
            if (dto.Type == 0)
            {
                sql_cz_dss_grp += " ) a ";
            }
            sql_cz_dss_grp += " group by code,grp_code,grp_name";
            var cz_dss_grp = dapper.Select<dynamic>(sql_cz_dss_grp, new { dto.StartDate, dto.EndDate, dto.AreaID });

            string sql_cz_mdc_grp = @"select  code,min(remain) czje,name from(
select a.CategoryID code,d.Name,sum(remain) remain from sys_list a 
inner join list_chargeinfo b on a.ListID=b.ListID
inner join sys_category c on c.CategoryID=a.CategoryID
inner join (select CategoryID,`Name` from sys_category where IsGroup=1) d on c.PID=d.CategoryID
inner join sys_dip_para e on a.Group_Code=e.Disease_Code
group by a.CategoryID,d.Name";
            if (dto.Type == 1)
            {
                sql_cz_mdc_grp += " having sum(Remain)>=0) a";
            }
            if (dto.Type == -1)
            {
                sql_cz_mdc_grp += " having sum(Remain)<0) a";
            }
            if (dto.Type == 0)
            {
                sql_cz_mdc_grp += " ) a ";
            }
            sql_cz_mdc_grp += " group by code,name";
            var cz_mdc_grp = dapper.Select<dynamic>(sql_cz_mdc_grp, new { dto.StartDate, dto.EndDate, dto.AreaID });

            var result = from x in list
                         join a in jc on x.code equals a.code
                         join b in jy on x.code equals b.code
                         join c in yf on x.code equals c.code
                         join d in hcf on x.code equals d.code
                         join e in cz_dc on x.code equals e.code
                         join f in cz_dss_grp on x.code equals f.code
                         join g in cz_mdc_grp on x.code equals g.code
                         select new DTO.Statistic.Category.OutPageList()
                         {
                             cjfy = x.cjfy,
                             jczb = Math.Round(Convert.ToDecimal(a.jcf) / x.total, 4) * 100,
                             cjjy = x.cjjy,
                             cnt = x.cnt,
                             code = x.code,
                             hzb = Math.Round(Convert.ToDecimal(d.hcf) / x.total, 4) * 100,
                             jyzb = Math.Round(Convert.ToDecimal(b.jyf) / x.total, 4) * 100,
                             name = x.name,
                             remain = x.remain,
                             total = x.total,
                             yzb = Math.Round(Convert.ToDecimal(c.yf) / x.total, 4) * 100,
                             max_dc = e.doc_name,
                             max_dss_grp = f.grp_name,
                             max_mdc_grp = g.name,
                             zqz = x.zqz
                         };

            return (result.ToList(), total);
        }
        public OutPart3 Part3(InOverview dto)
        {
            var dt1 = Convert.ToDateTime(dto.StartDate);
            var dt2 = Convert.ToDateTime(dto.EndDate);
            string dt = dt1.Year == dt2.Year && dt1.Month == dt2.Month ? "%m-%d" : "%Y-%m";
            var paras = new { dto.StartDate, dto.EndDate, dto.AreaID,dt };
            string sql1 = @"select count(1) value,DATE_FORMAT(SettleDate, @dt) fmt_date,'总病例数' type from sys_list 
 where SettleDate>=@StartDate and SettleDate<=@EndDate and AreaID=@AreaID group by fmt_date";
            var list1 = dapper.Select<BarLine>(sql1, paras);

            string sql2 = @"select count(1) value,DATE_FORMAT(SettleDate, @dt) fmt_date,'入组病例数' type from sys_list 
where Group_Code!='' and Group_Code!='0000' and SettleDate>=@StartDate and SettleDate<=@EndDate and AreaID=@AreaID
group by fmt_date";
            var list2 = dapper.Select<BarLine>(sql2, paras);

            var list3 = new List<BarLine>();
            foreach (var item in list1)
            {
                var ruzu = list2.SingleOrDefault(x => x.fmt_date == item.fmt_date);
                if (ruzu != null)
                {
                    list3.Add(new BarLine() { value = (ruzu.value / item.value) * 100, type = "入组率", fmt_date = item.fmt_date });
                }
                else
                {
                    list3.Add(new BarLine() { value = 0, type = "入组率", fmt_date = item.fmt_date });
                }
            }

            string sql4 = @"select count(distinct a.ListID) value, DATE_FORMAT(SettleDate, @dt) fmt_date,'全部手术' type from sys_list a 
left join diaginfo_operitem b on a.ListID=b.ListID
where b.OperCode!='' and SettleDate>=@StartDate and SettleDate<=@EndDate and AreaID=@AreaID
group by fmt_date";
            var list4 = dapper.Select<BarLine>(sql4, paras);

            string sql5 = @"select count(distinct a.ListID) value, DATE_FORMAT(SettleDate, @dt) fmt_date,'四级手术' type from sys_list a
left join diaginfo_operitem b on a.ListID=b.ListID
where b.OperCode in (select Code1 from sys_policyenum where `Group`='手术信息库' and Extra3='四级')
 and SettleDate>=@StartDate and SettleDate<=@EndDate and AreaID=@AreaID
group by fmt_date";
            var list5 = dapper.Select<BarLine>(sql5, paras);

            var list6 = new List<BarLine>();
            foreach (var item in list4)
            {
                var siji = list5.SingleOrDefault(x => x.fmt_date == item.fmt_date);
                if (siji != null)
                {
                    list6.Add(new BarLine() { value = (siji.value / item.value) * 100, type = "四级手术占比", fmt_date = item.fmt_date });
                }
                else
                {
                    list6.Add(new BarLine() { value = 0, type = "四级手术占比", fmt_date = item.fmt_date });
                }
            }

            string sql7 = @"select count(1)*sum(b.Score) value,DATE_FORMAT(SettleDate, @dt) fmt_date,'权重*病例数' type from sys_list a
inner join sys_dip_para b on a.Group_Code=b.Disease_Code
where SettleDate>=@StartDate and SettleDate<=@EndDate and AreaID=@AreaID
group by fmt_date order by fmt_date";
            var list7 = dapper.Select<BarLine1>(sql7, paras);

            string sql8 = "select  count(1) from sys_list where SettleDate<=NOW() and SettleDate>=@StartDate and SettleDate<=@EndDate and AreaID=@AreaID";
            int total = Convert.ToInt32(dapper.ExecuteScalar(sql8, paras));

            list7.ToList().ForEach(x => x.value = Math.Round(x.value / (decimal)total, 2));

            string sql9 = @"select count(1)*sum(b.Score) value,c.`Name` type,'CMI' fmt_date from sys_list a
inner join sys_dip_para b on a.Group_Code=b.Disease_Code
inner join sys_category c on a.CategoryID=c.CategoryID
where SettleDate>=@StartDate and SettleDate<=@EndDate and AreaID=@AreaID
group by c.`Name`";
            var list9 = dapper.Select<BarLine1>(sql9, paras);
            list9.ToList().ForEach(x => x.value = Math.Round(x.value / (decimal)total, 2));

            string sql10 = @"select sum(Remain) value,c.`Name` type,'盈亏' fmt_date  from sys_list a
inner join list_chargeinfo b on a.ListID=b.listID
inner join sys_category c on a.CategoryID=c.CategoryID
where SettleDate>=@StartDate and SettleDate<=@EndDate and AreaID=@AreaID
group by c.`Name`";
            var list10 = dapper.Select<BarLine1>(sql10, paras);

            var result = new OutPart3()
            {
                BarLine1 = list1.Concat(list2).Concat(list3).ToList(),
                BarLine2 = list4.Concat(list5).Concat(list6).ToList(),
                Line = list7.ToList(),
                Point = list9.Concat(list10).ToList(),
            };
            return result;
        }
        public OutPart4 Part4(InOverview dto)
        {
            var dt1 = Convert.ToDateTime(dto.StartDate);
            var dt2 = Convert.ToDateTime(dto.EndDate);
            string dt = dt1.Year == dt2.Year && dt1.Month == dt2.Month ? "%m-%d" : "%Y-%m";
            var paras = new { dto.StartDate, dto.EndDate, dto.AreaID,dt };
            string sql1 = @"select round((avg(c.InDays)/b.ZYTSBGZ)*count(1),2) value,DATE_FORMAT(SettleDate, @dt) fmt_date,'时间消耗指数' type from sys_list a
inner join sys_dip_para b on a.Group_Code=b.Disease_Code
inner join list_diaginfo c on a.ListID=c.ListID
 where SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID
group by fmt_date,ZYTSBGZ
order by fmt_date";
            var list1 = dapper.Select<BarLine1>(sql1, paras);

            string sql2 = @"select round((avg(c.Total)/b.ZGFFBZ)*count(1),2)  value,DATE_FORMAT(SettleDate, @dt) fmt_date,'费用消耗指数' type from sys_list a
inner join sys_dip_para b on a.Group_Code=b.Disease_Code
inner join list_chargeinfo c on a.ListID=c.ListID
where SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID
group by fmt_date,ZGFFBZ
order by fmt_date";
            var list2 = dapper.Select<BarLine1>(sql2, paras);

            string sql3 = @"select round(avg(c.InDays),2) value,DATE_FORMAT(SettleDate, @dt) fmt_date,'平均住院日' type from sys_list a
inner join sys_dip_para b on a.Group_Code=b.Disease_Code
inner join list_diaginfo c on a.ListID=c.ListID
where SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID
group by fmt_date
order by fmt_date";
            var list3 = dapper.Select<BarLine1>(sql3, paras);

            string sql4 = @"select round(sum(b.Total)/count(1),2) value,DATE_FORMAT(SettleDate, @dt) fmt_date,'次均费用' type from sys_list a
inner join list_chargeinfo b on a.ListID=b.ListID
where SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID
group by fmt_date
order by fmt_date";
            var list4 = dapper.Select<BarLine1>(sql4, paras);

            var result = new OutPart4()
            {
                Line1 = list1.Concat(list2).ToList(),
                Line2 = list3.Concat(list4).ToList()
            };
            return result;
        }
        public (List<DTO.Statistic.MedicalGroup.OutPageList> list, int total) MedicalGroupPageList(DTO.Statistic.MedicalGroup.InPageList dto)
        {
            string sql = @"select e.name name,e.CategoryID code,sum(total) total,count(a.ListID) cnt,sum(Remain) remain,sum(score) zqz,round(sum(Total)/count(distinct a.ListID),2) cjfy,round(sum(Remain)/count(distinct a.ListID),2) cjjy from sys_list a 
left join list_chargeinfo b on a.ListID=b.ListID
inner join sys_dip_para c on a.Group_Code=c.Disease_Code
inner join sys_category d on d.CategoryID=a.CategoryID
inner join (select CategoryID,`Name` from sys_category where IsGroup=1) e on d.PID=e.CategoryID
where SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql += " and a.CategoryID=@CategoryID ";
            }
            sql += " group by e.name,e.CategoryID ";
            if (dto.Type == 1)
            {
                sql += " having remain>=0 ";
            }
            if (dto.Type == -1)
            {
                sql += " having remain<0 ";
            }
            if (!string.IsNullOrEmpty(dto.Col) && !string.IsNullOrEmpty(dto.Sort))
            {
                if (dto.Sort.Equals("normal"))
                {
                    sql += " order by @Col ";
                }
                else
                {
                    sql += " order by @Col @Sort ";
                }
            }
            sql += " limit @PageSize OFFSET @PageNo ";

            var list = dapper.Select<DTO.Statistic.MedicalGroup.OutPageList>(sql, new { dto.CategoryID, dto.StartDate, dto.EndDate, dto.Col, dto.Sort, dto.AreaID, dto.PageSize, PageNo = (dto.PageNo - 1) * dto.PageSize });

            string sql1 = @"select count(1) from (
select count(1) cnt,e.Name from sys_list a 
left join list_chargeinfo b on a.ListID=b.ListID
inner join sys_dip_para c on a.Group_Code=c.Disease_Code
inner join sys_category d on d.CategoryID=a.CategoryID
inner join (select CategoryID,`Name` from sys_category where IsGroup=1) e on d.PID=e.CategoryID
where SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql1 += " and a.CategoryID=@CategoryID ";
            }
            sql1 += " group by e.Name ";
            if (dto.Type == 1)
            {
                sql1 += " having sum(Remain)>=0) a ";
            }
            if (dto.Type == -1)
            {
                sql1 += " having sum(Remain)<0) a ";
            }
            if (dto.Type == 0)
            {
                sql1 += ") a ";
            }
            int total = Convert.ToInt32(dapper.ExecuteScalar(sql1, new { dto.CategoryID, dto.StartDate, dto.EndDate, dto.AreaID }));

            string sql_jc = @"select e.CategoryID code,e.Name,sum(Amount) jcf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
inner join list_chargeinfo c on c.ListID=a.ListID
inner join sys_category d on d.CategoryID=a.CategoryID
inner join (select CategoryID,`Name` from sys_category where IsGroup=1) e on d.PID=e.CategoryID
where b.ItemName='检查费' and SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql_jc += " and a.CategoryID=@CategoryID ";
            }
            sql_jc += "group by e.CategoryID,e.Name ";
            if (dto.Type == 1)
            {
                sql_jc += " having sum(Remain)>=0 ";
            }
            if (dto.Type == -1)
            {
                sql_jc += " having sum(Remain)<0 ";
            }
            if (dto.Type == 0)
            {
                sql_jc += "";
            }
            var jc = dapper.Select<dynamic>(sql_jc, new { dto.CategoryID, dto.StartDate, dto.EndDate, dto.AreaID });

            string sql_jy = @"select e.CategoryID code,e.Name,sum(Amount) jcf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
inner join list_chargeinfo c on c.ListID=a.ListID
inner join sys_category d on d.CategoryID=a.CategoryID
inner join (select CategoryID,`Name` from sys_category where IsGroup=1) e on d.PID=e.CategoryID
where b.ItemName='化验费' and SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql_jy += " and a.CategoryID=@CategoryID ";
            }
            sql_jy += " group by e.CategoryID,e.Name ";
            if (dto.Type == 1)
            {
                sql_jy += " having sum(Remain)>=0";
            }
            if (dto.Type == -1)
            {
                sql_jy += " having sum(Remain)<0";
            }
            if (dto.Type == 0)
            {
                sql_jy += "";
            }
            var jy = dapper.Select<dynamic>(sql_jy, new { dto.CategoryID, dto.StartDate, dto.EndDate, dto.AreaID });

            string sql_yf = @"select e.CategoryID code,e.Name,sum(Amount) jcf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
inner join list_chargeinfo c on c.ListID=a.ListID
inner join sys_category d on d.CategoryID=a.CategoryID
inner join (select CategoryID,`Name` from sys_category where IsGroup=1) e on d.PID=e.CategoryID
where  b.ItemName in ('西药费','中药饮片费','中成药费') and SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql_yf += " and a.CategoryID=@CategoryID ";
            }
            sql_yf += " group by e.CategoryID,e.Name ";
            if (dto.Type == 1)
            {
                sql_yf += " having sum(Remain)>=0";
            }
            if (dto.Type == -1)
            {
                sql_yf += " having sum(Remain)<0";
            }
            if (dto.Type == 0)
            {
                sql_yf += "";
            }
            var yf = dapper.Select<dynamic>(sql_yf, new { dto.CategoryID, dto.StartDate, dto.EndDate, dto.AreaID });

            string sql_hcf = @"select e.CategoryID code,e.Name,sum(Amount) jcf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
inner join list_chargeinfo c on c.ListID=a.ListID
inner join sys_category d on d.CategoryID=a.CategoryID
inner join (select CategoryID,`Name` from sys_category where IsGroup=1) e on d.PID=e.CategoryID
where b.ItemName='卫生材料费' and SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql_hcf += " and a.CategoryID=@CategoryID ";
            }
            sql_hcf += " group by e.CategoryID,e.Name ";
            if (dto.Type == 1)
            {
                sql_hcf += " having sum(Remain)>=0";
            }
            if (dto.Type == -1)
            {
                sql_hcf += " having sum(Remain)<0";
            }
            if (dto.Type == 0)
            {
                sql_hcf += "";
            }
            var hcf = dapper.Select<dynamic>(sql_hcf, new { dto.CategoryID, dto.StartDate, dto.EndDate, dto.AreaID });


            string sql_cz_dc = @"SELECT a.code,a.name,a.remain czje,CONCAT(a.DcName,' - ',a.DcCode) doc_name
FROM (select e.CategoryID code,e.Name,sum(remain) remain,c.DcName,c.DcCode from sys_list a 
inner join list_chargeinfo b on a.ListID=b.ListID
inner join list_personinfo c on a.ListID=c.ListID
inner join sys_category d on d.CategoryID=a.CategoryID
inner join (select CategoryID,`Name` from sys_category where IsGroup=1) e on d.PID=e.CategoryID
where SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql_cz_dc += " and a.CategoryID=@CategoryID ";
            }
            sql_cz_dc += " group by e.CategoryID,e.Name,c.DcName,c.DcCode";
            if (dto.Type == 1)
            {
                sql_cz_dc += " having sum(Remain)>=0) a";
            }
            if (dto.Type == -1)
            {
                sql_cz_dc += " having sum(Remain)<0) a";
            }
            if (dto.Type == 0)
            {
                sql_cz_dc += ") a";
            }
            sql_cz_dc += @" INNER JOIN (
    SELECT code, MIN(remain) as min_remain
    FROM (select e.CategoryID code,e.Name,sum(remain) remain,c.DcName,c.DcCode from sys_list a 
inner join list_chargeinfo b on a.ListID=b.ListID
inner join list_personinfo c on a.ListID=c.ListID
inner join sys_category d on d.CategoryID=a.CategoryID
inner join (select CategoryID,`Name` from sys_category where IsGroup=1) e on d.PID=e.CategoryID
where SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql_cz_dc += " and a.CategoryID=@CategoryID ";
            }
            sql_cz_dc += " group by e.CategoryID,e.Name,c.DcName,c.DcCode ";
            if (dto.Type == 1)
            {
                sql_cz_dc += " having sum(Remain)>=0) b ";
            }
            if (dto.Type == -1)
            {
                sql_cz_dc += " having sum(Remain)<0) b ";
            }
            if (dto.Type == 0)
            {
                sql_cz_dc += ") b";
            }
            sql_cz_dc += @" GROUP BY code
) b ON a.code = b.code AND a.remain = b.min_remain";
            var cz_dc = dapper.Select<dynamic>(sql_cz_dc, new { dto.CategoryID, dto.StartDate, dto.EndDate, dto.AreaID });


            string sql_cz_dss_grp = @"SELECT t1.code,t1.name,t1.remain czje
FROM (select e.CategoryID code,e.Name,Group_Code grp_code,Group_Name grp_name,sum(remain) remain from sys_list a 
inner join list_chargeinfo b on a.ListID=b.ListID
inner join sys_category d on d.CategoryID=a.CategoryID
inner join (select CategoryID,`Name` from sys_category where IsGroup=1) e on d.PID=e.CategoryID
where SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql_cz_dss_grp += " and a.CategoryID=@CategoryID ";
            }
            sql_cz_dss_grp += " group by e.CategoryID,e.Name,Group_Code,Group_Name ";
            if (dto.Type == 1)
            {
                sql_cz_dss_grp += " having sum(Remain)>=0) t1 ";
            }
            if (dto.Type == -1)
            {
                sql_cz_dss_grp += " having sum(Remain)<0) t1 ";
            }
            if (dto.Type == 0)
            {
                sql_cz_dss_grp += ") t1 ";
            }
            sql_cz_dss_grp += @" INNER JOIN (
    SELECT code, MIN(remain) as min_remain
    FROM (select e.CategoryID code,e.Name,Group_Code grp_code,Group_Name grp_name,sum(remain) remain from sys_list a 
inner join list_chargeinfo b on a.ListID=b.ListID
inner join sys_category d on d.CategoryID=a.CategoryID
inner join (select CategoryID,`Name` from sys_category where IsGroup=1) e on d.PID=e.CategoryID 
where SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql_cz_dss_grp += " and a.CategoryID=@CategoryID ";
            }
            sql_cz_dss_grp += " group by e.CategoryID,e.Name,Group_Code,Group_Name ";
            if (dto.Type == 1)
            {
                sql_cz_dss_grp += " having sum(Remain)>=0) t2 ";
            }
            if (dto.Type == -1)
            {
                sql_cz_dss_grp += " having sum(Remain)<0) t2 ";
            }
            if (dto.Type == 0)
            {
                sql_cz_dss_grp += ") t2 ";
            }
            sql_cz_dss_grp += @" GROUP BY code
) t2 ON t1.code = t2.code AND t1.remain = t2.min_remain";
            var cz_dss_grp = dapper.Select<dynamic>(sql_cz_dss_grp, new { dto.CategoryID, dto.StartDate, dto.EndDate, dto.AreaID });

            var result = from x in list
                         join a in jc on x.code equals a.code
                         join b in jy on x.code equals b.code
                         join c in yf on x.code equals c.code
                         join d in hcf on x.code equals d.code
                         join e in cz_dc on x.code equals e.code
                         join f in cz_dss_grp on x.code equals f.code
                         select new DTO.Statistic.MedicalGroup.OutPageList()
                         {
                             cjfy = x.cjfy,
                             jczb = Math.Round(Convert.ToDecimal(a.jcf) / x.total, 4) * 100,
                             cjjy = x.cjjy,
                             cnt = x.cnt,
                             code = x.code,
                             hzb = Math.Round(Convert.ToDecimal(d.hcf) / x.total, 4) * 100,
                             jyzb = Math.Round(Convert.ToDecimal(b.jyf) / x.total, 4) * 100,
                             name = x.name,
                             remain = x.remain,
                             total = x.total,
                             yzb = Math.Round(Convert.ToDecimal(c.yf) / x.total, 4) * 100,
                             max_dc = e.doc_name,
                             max_dss_grp = f.name,
                             zqz = x.zqz,
                         };

            return (result.ToList(), total);
        }
        public (List<DTO.Statistic.Category.OutDetailPageList> list, int total) DetailCategoryPageList(DTO.Statistic.Category.InDetailPageList dto)
        {
            string sql = @"select Group_Name name,Group_Code code,count(a.ListID) cnt,sum(Remain) remain,sum(Total) total,c.ZGFFBZ,JMFFBZ,round(sum(Total)/count(distinct a.ListID),2) cjfy,round(sum(Remain)/count(distinct a.ListID),2) cjjy from sys_list a 
left join list_chargeinfo b on a.ListID=b.ListID
inner join sys_dip_para c on a.Group_Code=c.Disease_Code
where SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrWhiteSpace(dto.CategoryID))
            {
                sql += " and CategoryID=@CategoryID ";
            }
            sql += " group by Group_Name,Group_Code,c.ZGFFBZ,JMFFBZ limit @PageSize OFFSET @PageNo ";
            var list = dapper.Select<DTO.Statistic.Category.OutDetailPageList>(sql, new { dto.StartDate, dto.EndDate, dto.CategoryID, dto.AreaID, dto.PageSize, PageNo = (dto.PageNo - 1) * dto.PageSize });

            string sql1 = @"select count(1) from (select count(1),Group_Code from sys_list a 
inner join sys_dip_para c on a.Group_Code=c.Disease_Code
where SettleDate>=@StartDate and SettleDate<=@EndDate and AreaID=@AreaID";
            if (!string.IsNullOrWhiteSpace(dto.CategoryID))
            {
                sql1 += " and CategoryID=@CategoryID ";
            }
            sql1 += " group by Group_Code) a ";
            string sql_jc = @"select Group_Code code,sum(Amount) jcf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
where b.ItemName='检查费' and SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrWhiteSpace(dto.CategoryID))
            {
                sql_jc += " and CategoryID=@CategoryID ";
            }
            sql_jc += " group by Group_Code ";
            var jc = dapper.Select<dynamic>(sql_jc, new { dto.StartDate, dto.EndDate, dto.CategoryID, dto.AreaID });

            string sql_jy = @"select Group_Code code,sum(Amount) jyf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
where b.ItemName='化验费' and SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrWhiteSpace(dto.CategoryID))
            {
                sql_jy += " and CategoryID=@CategoryID ";
            }
            sql_jy += " group by Group_Code ";
            var jy = dapper.Select<dynamic>(sql_jy, new { dto.StartDate, dto.EndDate, dto.CategoryID, dto.AreaID });

            string sql_yf = @"select Group_Code code,sum(Amount) yf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
where b.ItemName in ('西药费','中药饮片费','中成药费') and SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrWhiteSpace(dto.CategoryID))
            {
                sql_yf += " and CategoryID=@CategoryID ";
            }
            sql_yf += " group by Group_Code ";
            var yf = dapper.Select<dynamic>(sql_yf, new { dto.StartDate, dto.EndDate, dto.CategoryID, dto.AreaID });

            string sql_hcf = @"select Group_Code code,sum(Amount) hcf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
where b.ItemName='卫生材料费' and SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrWhiteSpace(dto.CategoryID))
            {
                sql_hcf += " and CategoryID=@CategoryID ";
            }
            sql_hcf += " group by Group_Code ";
            var hcf = dapper.Select<dynamic>(sql_hcf, new { dto.StartDate, dto.EndDate, dto.CategoryID, dto.AreaID });

            var result = from x in list
                         join a in jc on x.code equals a.code
                         join b in jy on x.code equals b.code
                         join c in yf on x.code equals c.code
                         join d in hcf on x.code equals d.code
                         select new DTO.Statistic.Category.OutDetailPageList()
                         {
                             cjfy = x.cjfy,
                             jczb = Math.Round(Convert.ToDecimal(a.jcf) / x.total, 4) * 100,
                             cjjy = x.cjjy,
                             cnt = x.cnt,
                             code = x.code,
                             hzb = Math.Round(Convert.ToDecimal(d.hcf) / x.total, 4) * 100,
                             JMFFBZ = x.JMFFBZ,
                             jyzb = Math.Round(Convert.ToDecimal(b.jyf) / x.total, 4) * 100,
                             name = x.name,
                             remain = x.remain,
                             total = x.total,
                             yzb = Math.Round(Convert.ToDecimal(c.yf) / x.total, 4) * 100,
                             ZGFFBZ = x.ZGFFBZ
                         };

            int total = Convert.ToInt32(dapper.ExecuteScalar(sql1, new { dto.StartDate, dto.EndDate, dto.CategoryID, dto.AreaID }));
            return (list.ToList(), total);
        }
        public (List<DTO.Statistic.MedicalGroup.OutDetailPageList> list, int total) DetailMedicalGroupPageList(DTO.Statistic.MedicalGroup.InDetailPageList dto)
        {
            string sql = @"select Group_Name name,Group_Code code,count(a.ListID) cnt,sum(Remain) remain,sum(Total) total,c.ZGFFBZ,JMFFBZ,sum(score) zqz,round(sum(Total)/count(distinct a.ListID),2) cjfy,round(sum(Remain)/count(distinct a.ListID),2) cjjy from sys_list a 
left join list_chargeinfo b on a.ListID=b.ListID
inner join sys_dip_para c on a.Group_Code=c.Disease_Code
where SettleDate>=@StartDate and SettleDate<=@EndDate and CategoryID in (select CategoryID from sys_category where pid=@CategoryID) and a.AreaID=@AreaID
group by Group_Name,Group_Code,c.ZGFFBZ,JMFFBZ 
order by @Col @Sort
limit @PageSize OFFSET @PageNo";
            var list = dapper.Select<DTO.Statistic.MedicalGroup.OutDetailPageList>(sql, new { dto.StartDate, dto.EndDate, dto.CategoryID, dto.AreaID, dto.PageSize, PageNo = (dto.PageNo - 1) * dto.PageSize, dto.Col, dto.Sort });

            string sql1 = @"select count(1) from sys_list a 
inner join sys_dip_para c on a.Group_Code=c.Disease_Code
where SettleDate>=@StartDate and SettleDate<=@EndDate and CategoryID in (select CategoryID from sys_category where pid=@CategoryID) and a.AreaID=@AreaID";
            int total = Convert.ToInt32(dapper.ExecuteScalar(sql1, new { dto.StartDate, dto.EndDate, dto.CategoryID, dto.AreaID }));

            string sql_jc = @"select Group_Code code,sum(Amount) jcf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
where b.ItemName='检查费' and SettleDate>=@StartDate and SettleDate<=@EndDate and CategoryID in (select CategoryID from sys_category where pid=@CategoryID) and a.AreaID=@AreaID
group by Group_Code";
            var jc = dapper.Select<dynamic>(sql_jc, new { dto.StartDate, dto.EndDate, dto.CategoryID, dto.AreaID });

            string sql_jy = @"select Group_Code code,sum(Amount) jyf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
where b.ItemName='化验费' and SettleDate>=@StartDate and SettleDate<=@EndDate and CategoryID in (select CategoryID from sys_category where pid=@CategoryID) and a.AreaID=@AreaID
group by Group_Code";
            var jy = dapper.Select<dynamic>(sql_jy, new { dto.StartDate, dto.EndDate, dto.CategoryID, dto.AreaID });

            string sql_yf = @"select Group_Code code,sum(Amount) yf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
where b.ItemName in ('西药费','中药饮片费','中成药费') and SettleDate>=@StartDate and SettleDate<=@EndDate and CategoryID in (select CategoryID from sys_category where pid=@CategoryID) and a.AreaID=@AreaID
group by Group_Code";
            var yf = dapper.Select<dynamic>(sql_yf, new { dto.StartDate, dto.EndDate, dto.CategoryID, dto.AreaID });

            string sql_hcf = @"select Group_Code code,sum(Amount) hcf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
where b.ItemName='卫生材料费' and SettleDate>=@StartDate and SettleDate<=@EndDate and CategoryID in (select CategoryID from sys_category where pid=@CategoryID) and a.AreaID=@AreaID
group by Group_Code";
            var hcf = dapper.Select<dynamic>(sql_hcf, new { dto.StartDate, dto.EndDate, dto.CategoryID, dto.AreaID });


            string sql_cz = @"select  code,min(remain) czje,CONCAT(DcName,' - ',DcCode) doc_name from(
select Group_Code code,sum(remain) remain,c.DcName,c.DcCode from sys_list a 
inner join list_chargeinfo b on a.ListID=b.ListID
inner join list_personinfo c on a.ListID=c.ListID
where SettleDate>=@StartDate and SettleDate<=@EndDate and CategoryID in (select CategoryID from sys_category where pid=@CategoryID) and a.AreaID=@AreaID
group by Group_Code,c.DcName,c.DcCode ) a group by code,CONCAT(DcName,' - ',DcCode)";
            var cz = dapper.Select<dynamic>(sql_cz, new { dto.StartDate, dto.EndDate, dto.CategoryID, dto.AreaID });
            var result = from x in list
                         join a in jc on x.code equals a.code
                         join b in jy on x.code equals b.code
                         join c in yf on x.code equals c.code
                         join d in hcf on x.code equals d.code
                         join e in cz on x.code equals e.code
                         select new DTO.Statistic.MedicalGroup.OutDetailPageList()
                         {
                             zqz=x.zqz,
                             cjfy = x.cjfy,
                             jczb = Math.Round(Convert.ToDecimal(a.jcf) / x.total, 4) * 100,
                             cjjy = x.cjjy,
                             cnt = x.cnt,
                             code = x.code,
                             hzb = Math.Round(Convert.ToDecimal(d.hcf) / x.total, 4) * 100,
                             JMFFBZ = x.JMFFBZ,
                             jyzb = Math.Round(Convert.ToDecimal(b.jyf) / x.total, 4) * 100,
                             name = x.name,
                             remain = x.remain,
                             total = x.total,
                             yzb = Math.Round(Convert.ToDecimal(c.yf) / x.total, 4) * 100,
                             ZGFFBZ = x.ZGFFBZ,
                             czje = Convert.ToDecimal(e.czje),
                             doc_name = e.doc_name,
                         };

            return (result.ToList(), total);
        }
        public (List<DTO.Statistic.Doctor.OutPageList> list, int total) DoctorPageList(DTO.Statistic.Doctor.InPageList dto)
        {
            string sql = @"select d.DcName name,d.DcCode code,sum(total) total,count(a.ListID) cnt,sum(Remain) remain,sum(score) zqz,round(sum(Total)/count(distinct a.ListID),2) cjfy,round(sum(Remain)/count(distinct a.ListID),2) cjjy from sys_list a 
left join list_chargeinfo b on a.ListID=b.ListID
inner join sys_dip_para c on a.Group_Code=c.Disease_Code
inner join list_personinfo d on d.ListID=a.ListID
where SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql += " and a.CategoryID=@CategoryID ";
            }
            sql += " group by d.DcName,d.DcCode ";
            if (dto.Type == 1)
            {
                sql += " having remain>=0 ";
            }
            if (dto.Type == -1)
            {
                sql += " having remain<0 ";
            }
            if (!string.IsNullOrEmpty(dto.Col) && !string.IsNullOrEmpty(dto.Sort))
            {
                if (dto.Sort.Equals("normal"))
                {
                    sql += " order by @Col ";
                }
                else
                {
                    sql += " order by @Col @Sort ";
                }
            }
            sql += " limit @PageSize OFFSET @PageNo ";

            var list = dapper.Select<DTO.Statistic.Doctor.OutPageList>(sql, new { dto.StartDate, dto.EndDate, dto.Col, dto.Sort, dto.CategoryID, dto.AreaID, dto.PageSize, PageNo = (dto.PageNo - 1) * dto.PageSize });

            string sql1 = @"select count(1) from (
select count(1) cnt,d.DcCode from sys_list a 
left join list_chargeinfo b on a.ListID=b.ListID
inner join sys_dip_para c on a.Group_Code=c.Disease_Code
inner join list_personinfo d on d.ListID=a.ListID
where SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql1 += " and a.CategoryID=@CategoryID ";
            }
            sql1 += " group by d.DcCode ";
            if (dto.Type == 1)
            {
                sql1 += " having sum(Remain)>=0) a";
            }
            if (dto.Type == -1)
            {
                sql1 += " having sum(Remain)<0) a";
            }
            if (dto.Type == 0)
            {
                sql1 += ") a ";
            }
            int total = Convert.ToInt32(dapper.ExecuteScalar(sql1, new { dto.StartDate, dto.EndDate, dto.AreaID, dto.CategoryID }));

            string sql_jc = @"select d.DcName name,d.DcCode code,sum(Amount) jcf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
inner join list_chargeinfo c on c.ListID=a.ListID
inner join list_personinfo d on d.ListID=a.ListID
where b.ItemName='检查费' and SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql_jc += " and a.CategoryID=@CategoryID ";
            }
            sql_jc += " group by d.DcName,d.DcCode ";
            if (dto.Type == 1)
            {
                sql_jc += " having sum(Remain)>=0 ";
            }
            if (dto.Type == -1)
            {
                sql_jc += " having sum(Remain)<0 ";
            }
            if (dto.Type == 0)
            {
                sql_jc += "";
            }
            var jc = dapper.Select<dynamic>(sql_jc, new { dto.StartDate, dto.EndDate, dto.AreaID, dto.CategoryID });

            string sql_jy = @"select d.DcName name,d.DcCode code,sum(Amount) jcf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
inner join list_chargeinfo c on c.ListID=a.ListID
inner join list_personinfo d on d.ListID=a.ListID
where b.ItemName='化验费' and SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql_jy += " and a.CategoryID=@CategoryID ";
            }
            sql_jy += " group by d.DcName,d.DcCode ";
            if (dto.Type == 1)
            {
                sql_jy += " having sum(Remain)>=0 ";
            }
            if (dto.Type == -1)
            {
                sql_jy += " having sum(Remain)<0 ";
            }
            if (dto.Type == 0)
            {
                sql_jy += "";
            }
            var jy = dapper.Select<dynamic>(sql_jy, new { dto.StartDate, dto.EndDate, dto.AreaID, dto.CategoryID });

            string sql_yf = @"select d.DcName name,d.DcCode code,sum(Amount) jcf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
inner join list_chargeinfo c on c.ListID=a.ListID
inner join list_personinfo d on d.ListID=a.ListID
where  b.ItemName in ('西药费','中药饮片费','中成药费') and SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql_yf += " and a.CategoryID=@CategoryID ";
            }
            sql_yf += " group by d.DcName,d.DcCode ";
            if (dto.Type == 1)
            {
                sql_yf += " having sum(Remain)>=0 ";
            }
            if (dto.Type == -1)
            {
                sql_yf += " having sum(Remain)<0 ";
            }
            if (dto.Type == 0)
            {
                sql_yf += "";
            }
            var yf = dapper.Select<dynamic>(sql_yf, new { dto.StartDate, dto.EndDate, dto.AreaID });

            string sql_hcf = @"select d.DcName name,d.DcCode code,sum(Amount) jcf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
inner join list_chargeinfo c on c.ListID=a.ListID
inner join list_personinfo d on d.ListID=a.ListID
where b.ItemName='卫生材料费' and SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql_hcf += " and a.CategoryID=@CategoryID ";
            }
            sql_hcf += " group by d.DcName,d.DcCode ";
            if (dto.Type == 1)
            {
                sql_hcf += " having sum(Remain)>=0 ";
            }
            if (dto.Type == -1)
            {
                sql_hcf += " having sum(Remain)<0 ";
            }
            if (dto.Type == 0)
            {
                sql_hcf += "";
            }
            var hcf = dapper.Select<dynamic>(sql_hcf, new { dto.StartDate, dto.EndDate, dto.AreaID });

            var result = from x in list
                         join a in jc on x.code equals a.code
                         join b in jy on x.code equals b.code
                         join c in yf on x.code equals c.code
                         join d in hcf on x.code equals d.code
                         select new DTO.Statistic.Doctor.OutPageList()
                         {
                             cjfy = x.cjfy,
                             jczb = Math.Round(Convert.ToDecimal(a.jcf) / x.total, 4) * 100,
                             cjjy = x.cjjy,
                             cnt = x.cnt,
                             code = x.code,
                             hzb = Math.Round(Convert.ToDecimal(d.hcf) / x.total, 4) * 100,
                             jyzb = Math.Round(Convert.ToDecimal(b.jyf) / x.total, 4) * 100,
                             name = x.name,
                             remain = x.remain,
                             total = x.total,
                             yzb = Math.Round(Convert.ToDecimal(c.yf) / x.total, 4) * 100,
                             zqz = x.zqz,
                         };

            return (result.ToList(), total);
        }
        public (List<DTO.Statistic.Doctor.OutDetailPageList> list, int total) DetailDoctorPageList(DTO.Statistic.Doctor.InDetailPageList dto)
        {
            string sql = @"select Group_Name name,Group_Code code,count(a.ListID) cnt,sum(Remain) remain,sum(Total) total,c.ZGFFBZ,JMFFBZ,round(sum(Total)/count(distinct a.ListID),2) cjfy,round(sum(Remain)/count(distinct a.ListID),2) cjjy from sys_list a 
left join list_chargeinfo b on a.ListID=b.ListID
inner join sys_dip_para c on a.Group_Code=c.Disease_Code
left join list_personinfo d on a.ListID=d.ListID
where SettleDate>=@StartDate and SettleDate<=@EndDate and d.DcCode=@DcCode and a.AreaID=@AreaID
group by Group_Name,Group_Code,c.ZGFFBZ,JMFFBZ 
order by @Col @Sort
limit @PageSize OFFSET @PageNo";
            var list = dapper.Select<DTO.Statistic.MedicalGroup.OutDetailPageList>(sql, new { dto.StartDate, dto.EndDate, dto.DcCode, dto.AreaID, dto.PageSize, PageNo = (dto.PageNo - 1) * dto.PageSize, dto.Col, dto.Sort });

            string sql1 = @"select count(1) from sys_list a 
inner join sys_dip_para b on a.Group_Code=b.Disease_Code
left join list_personinfo c on a.ListID=c.ListID
where SettleDate>=@StartDate and SettleDate<=@EndDate and c.DcCode=@DcCode and a.AreaID=@AreaID";
            int total = Convert.ToInt32(dapper.ExecuteScalar(sql1, new { dto.StartDate, dto.EndDate, dto.DcCode, dto.AreaID }));

            string sql_jc = @"select Group_Code code,sum(Amount) jcf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
left join list_personinfo c on a.ListID=c.ListID
where b.ItemName='检查费' and SettleDate>=@StartDate and SettleDate<=@EndDate and c.DcCode=@DcCode and a.AreaID=@AreaID
group by Group_Code";
            var jc = dapper.Select<dynamic>(sql_jc, new { dto.StartDate, dto.EndDate, dto.DcCode, dto.AreaID });

            string sql_jy = @"select Group_Code code,sum(Amount) jyf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
left join list_personinfo c on a.ListID=c.ListID
where b.ItemName='化验费' and SettleDate>=@StartDate and SettleDate<=@EndDate and c.DcCode=@DcCode and a.AreaID=@AreaID
group by Group_Code";
            var jy = dapper.Select<dynamic>(sql_jy, new { dto.StartDate, dto.EndDate, dto.DcCode, dto.AreaID });

            string sql_yf = @"select Group_Code code,sum(Amount) yf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
left join list_personinfo c on a.ListID=c.ListID
where b.ItemName in ('西药费','中药饮片费','中成药费') and SettleDate>=@StartDate and SettleDate<=@EndDate and c.DcCode=@DcCode and a.AreaID=@AreaID
group by Group_Code";
            var yf = dapper.Select<dynamic>(sql_yf, new { dto.StartDate, dto.EndDate, dto.DcCode, dto.AreaID });

            string sql_hcf = @"select Group_Code code,sum(Amount) hcf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
left join list_personinfo c on a.ListID=c.ListID
where b.ItemName='卫生材料费' and SettleDate>=@StartDate and SettleDate<=@EndDate and c.DcCode=@DcCode and a.AreaID=@AreaID
group by Group_Code";
            var hcf = dapper.Select<dynamic>(sql_hcf, new { dto.StartDate, dto.EndDate, dto.DcCode, dto.AreaID });

            var result = from x in list
                         join a in jc on x.code equals a.code
                         join b in jy on x.code equals b.code
                         join c in yf on x.code equals c.code
                         join d in hcf on x.code equals d.code
                         select new DTO.Statistic.Doctor.OutDetailPageList()
                         {
                             cjfy = x.cjfy,
                             jczb = Math.Round(Convert.ToDecimal(a.jcf) / x.total, 4) * 100,
                             cjjy = x.cjjy,
                             cnt = x.cnt,
                             code = x.code,
                             hzb = Math.Round(Convert.ToDecimal(d.hcf) / x.total, 4) * 100,
                             JMFFBZ = x.JMFFBZ,
                             jyzb = Math.Round(Convert.ToDecimal(b.jyf) / x.total, 4) * 100,
                             name = x.name,
                             remain = x.remain,
                             total = x.total,
                             yzb = Math.Round(Convert.ToDecimal(c.yf) / x.total, 4) * 100,
                             ZGFFBZ = x.ZGFFBZ,
                         };

            return (result.ToList(), total);
        }
        public List<OutSelectList> GetSelectList(string keyword)
        {
            var result = ef.Set<Sys_DIP_Para>().Where(x => (EF.Functions.Like(x.PinYin, $"%{keyword}%")
                        || EF.Functions.Like(x.Disease_Code, $"%{keyword}%") || EF.Functions.Like(x.Disease_Name, $"%{keyword}%")))
                            .Select(x => new OutSelectList { GroupCode = x.Disease_Code, GroupName = x.Disease_Name })
                            .Take(10).ToList();
            return result;
        }
        public DTO.Statistic.Group.OutPart1 Part1(DTO.Statistic.Group.InGroup dto)
        {
            var paras = new { dto.StartDate, dto.EndDate, dto.CategoryID, dto.AreaID };
            string sql1 = @"select sum(cnt) value from (
select count(1) cnt,Group_Code from sys_list a 
left join list_chargeinfo b on a.ListID=b.ListID
left join sys_dip_para c on c.Disease_Code=a.Group_Code
where SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql1 += " and CategoryID=@CategoryID ";
            }
            sql1 += @" group by Group_Code
having sum(b.Remain)>=0) a";
            int yy = Convert.ToInt32(dapper.ExecuteScalar(sql1, paras));

            string sql2 = @"select sum(cnt) value from (
select count(1) cnt,Group_Code from sys_list a 
left join list_chargeinfo b on a.ListID=b.ListID
left join sys_dip_para c on c.Disease_Code=a.Group_Code
where SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql2 += " and CategoryID=@CategoryID ";
            }
            sql2 += @" group by Group_Code
having sum(b.Remain)<0) a";
            int ks = Convert.ToInt32(dapper.ExecuteScalar(sql2, paras));

            string sql3 = @"select sum(b.Remain) Amount,a.Group_Code,a.Group_Name  from sys_list a 
left join list_chargeinfo b on a.ListID=b.ListID
left join sys_dip_para c on c.Disease_Code=a.Group_Code
where SettleDate>= @StartDate and SettleDate<= @EndDate and a.AreaID = @AreaID ";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql3 += " and CategoryID=@CategoryID ";
            }
            sql3 += @" group by a.Group_Code,a.Group_Name
having sum(b.Remain)>=0
order by Amount desc
limit 5";

            var list3 = dapper.Select<DTO.Statistic.Group.OutPart1Bar>(sql3, paras);

            string sql4 = @"select sum(b.Remain) Amount,a.Group_Code,a.Group_Name  from sys_list a 
left join list_chargeinfo b on a.ListID=b.ListID
left join sys_dip_para c on c.Disease_Code=a.Group_Code
where SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql4 += " and CategoryID=@CategoryID ";
            }
            sql4 += @"group by a.Group_Code,a.Group_Name
having sum(b.Remain)<0
order by Amount desc
limit 5";

            var list4 = dapper.Select<DTO.Statistic.Group.OutPart1Bar>(sql4, paras);

            var result = new DTO.Statistic.Group.OutPart1()
            {
                yy = yy,
                ks = ks,
                Bar1 = list3.ToList(),
                Bar2 = list4.ToList(),
            };
            return result;
        }
        public (List<DTO.Statistic.Group.OutPageList> list, int total) GroupPageList(DTO.Statistic.Group.InPageList dto)
        {
            string sql = @"select Group_Name name,Group_Code code,sum(total) total,count(a.ListID) cnt,sum(Remain) remain,sum(score) zqz,round(sum(Total)/count(distinct a.ListID),2) cjfy,round(sum(Remain)/count(distinct a.ListID),2) cjjy from sys_list a 
left join list_chargeinfo b on a.ListID=b.ListID
inner join sys_dip_para c on a.Group_Code=c.Disease_Code
where SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql += " and a.CategoryID=@CategoryID ";
            }
            sql += "  group by Group_Name,Group_Code ";
            if (dto.Type == 1)
            {
                sql += " having remain>=0 ";
            }
            if (dto.Type == -1)
            {
                sql += " having remain<0 ";
            }
            if (!string.IsNullOrEmpty(dto.Col) && !string.IsNullOrEmpty(dto.Sort))
            {
                if (dto.Sort.Equals("normal"))
                {
                    sql += "order by @Col";
                }
                else
                {
                    sql += "order by @Col @Sort";
                }
            }
            sql += " limit @PageSize OFFSET @PageNo";

            var list = dapper.Select<DTO.Statistic.Category.OutPageList>(sql, new { dto.CategoryID, dto.StartDate, dto.EndDate, dto.Col, dto.Sort, dto.AreaID, dto.PageSize, PageNo = (dto.PageNo - 1) * dto.PageSize });

            string sql1 = @"select count(1) from (
select count(1) cnt,Group_Code from sys_list a 
left join list_chargeinfo b on a.ListID=b.ListID
inner join sys_dip_para c on a.Group_Code=c.Disease_Code
where SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql += " and a.CategoryID=@CategoryID ";
            }
            sql1 += " group by Group_Code ";
            if (dto.Type == 1)
            {
                sql += " having sum(Remain)>=0) a";
            }
            if (dto.Type == -1)
            {
                sql += " having sum(Remain)<0) a";
            }
            if (dto.Type == 0)
            {
                sql += " ) a";
            }
            int total = Convert.ToInt32(dapper.ExecuteScalar(sql1, new { dto.CategoryID, dto.StartDate, dto.EndDate, dto.AreaID }));

            string sql_jc = @"select Group_Code code,sum(Amount) jcf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
inner join list_chargeinfo c on c.ListID=a.ListID
inner join sys_dip_para d on a.Group_Code=d.Disease_Code
where b.ItemName='检查费' and SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql_jc += " and a.CategoryID=@CategoryID ";
            }
            sql_jc += " group by Group_Code ";
            if (dto.Type == 1)
            {
                sql_jc += " having sum(Remain)>=0) a";
            }
            if (dto.Type == -1)
            {
                sql_jc += " having sum(Remain)<0) a";
            }
            if (dto.Type == 0)
            {
                sql_jc += " ) a";
            }
            var jc = dapper.Select<dynamic>(sql_jc, new { dto.StartDate, dto.EndDate, dto.AreaID });

            string sql_jy = @"select Group_Code code,sum(Amount) jcf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
inner join list_chargeinfo c on c.ListID=a.ListID
inner join sys_dip_para d on a.Group_Code=d.Disease_Code
where b.ItemName='化验费' and SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql_jy += " and a.CategoryID=@CategoryID ";
            }
            sql_jy += " group by Group_Code";
            if (dto.Type == 1)
            {
                sql_jy += " having sum(Remain)>=0) a";
            }
            if (dto.Type == -1)
            {
                sql_jy += " having sum(Remain)<0) a";
            }
            if (dto.Type == 0)
            {
                sql_jy += " ) a";
            }
            var jy = dapper.Select<dynamic>(sql_jy, new { dto.StartDate, dto.EndDate, dto.AreaID });

            string sql_yf = @"select Group_Code code,sum(Amount) jcf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
inner join list_chargeinfo c on c.ListID=a.ListID
inner join sys_dip_para d on a.Group_Code=d.Disease_Code
where  b.ItemName in ('西药费','中药饮片费','中成药费') and SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql_yf += " and a.CategoryID=@CategoryID ";
            }
            sql_yf += " group by Group_Code";
            if (dto.Type == 1)
            {
                sql_yf += " having sum(Remain)>=0) a";
            }
            if (dto.Type == -1)
            {
                sql_yf += " having sum(Remain)<0) a";
            }
            if (dto.Type == 0)
            {
                sql_yf += " ) a";
            }
            var yf = dapper.Select<dynamic>(sql_yf, new { dto.StartDate, dto.EndDate, dto.AreaID });

            string sql_hcf = @"select Group_Code code,sum(Amount) jcf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
inner join list_chargeinfo c on c.ListID=a.ListID
inner join sys_dip_para d on a.Group_Code=d.Disease_Code
where b.ItemName='卫生材料费' and SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql_yf += " and a.CategoryID=@CategoryID ";
            }
            sql_yf += " group by Group_Code";
            if (dto.Type == 1)
            {
                sql_hcf += " having sum(Remain)>=0) a";
            }
            if (dto.Type == -1)
            {
                sql_hcf += " having sum(Remain)<0) a";
            }
            if (dto.Type == 0)
            {
                sql_hcf += " ) a";
            }
            var hcf = dapper.Select<dynamic>(sql_hcf, new { dto.StartDate, dto.EndDate, dto.AreaID });


            string sql_cz_dc = @"SELECT t1.code,t1.remain czje,CONCAT(t1.DcName,' - ',t1.DcCode) doc_name
FROM (select Group_Code code,sum(remain) remain,c.DcName,c.DcCode from sys_list a 
inner join list_chargeinfo b on a.ListID=b.ListID
inner join list_personinfo c on a.ListID=c.ListID
inner join sys_dip_para d on a.Group_Code=d.Disease_Code
where SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql_cz_dc += " and a.CategoryID=@CategoryID ";
            }
            sql_cz_dc += @"group by Group_Code,c.DcName,c.DcCode";
            if (dto.Type == 1)
            {
                sql_cz_dc += " having sum(Remain)>=0) t1";
            }
            if (dto.Type == -1)
            {
                sql_cz_dc += " having sum(Remain)<0) t1";
            }
            if (dto.Type == 0)
            {
                sql_cz_dc += " ) t1";
            }
            sql_cz_dc += @"INNER JOIN (
    SELECT code, MIN(remain) as min_remain
    FROM (select Group_Code code,sum(remain) remain,c.DcName,c.DcCode from sys_list a 
inner join list_chargeinfo b on a.ListID=b.ListID
inner join list_personinfo c on a.ListID=c.ListID
inner join sys_dip_para d on a.Group_Code=d.Disease_Code
where SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql_cz_dc += " and a.CategoryID=@CategoryID ";
            }
            sql_cz_dc += @"group by Group_Code,c.DcName,c.DcCode ";
            if (dto.Type == 1)
            {
                sql_cz_dc += " having sum(Remain)>=0) a";
            }
            if (dto.Type == -1)
            {
                sql_cz_dc += " having sum(Remain)<0) a";
            }
            if (dto.Type == 0)
            {
                sql_cz_dc += " ) a";
            }
            sql_cz_dc += @"GROUP BY code
) t2 ON t1.code = t2.code AND t1.remain = t2.min_remain";
            var cz_dc = dapper.Select<dynamic>(sql_cz_dc, new { dto.StartDate, dto.EndDate, dto.AreaID, dto.CategoryID });


            string sql_cz_ctg = @"SELECT t1.code,t1.name,t1.remain czje
FROM (select Group_Code code,CategoryName name,sum(remain) remain from sys_list a 
inner join list_chargeinfo b on a.ListID=b.ListID
inner join sys_dip_para c on c.DRG_Code=a.Disease_Code
where SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql_cz_ctg += " and a.CategoryID=@CategoryID ";
            }
            sql_cz_ctg += @"group by Group_Code,CategoryName ";
            if (dto.Type == 1)
            {
                sql_cz_ctg += " having sum(Remain)>=0) t1";
            }
            if (dto.Type == -1)
            {
                sql_cz_ctg += " having sum(Remain)<0) t1";
            }
            if (dto.Type == 0)
            {
                sql_cz_ctg += " ) t1";
            }
            sql_cz_ctg = @"INNER JOIN (
    SELECT code, MIN(remain) as min_remain
    FROM (select Group_Code code,CategoryName name,sum(remain) remain from sys_list a 
inner join list_chargeinfo b on a.ListID=b.ListID
inner join sys_dip_para c on c.DRG_Code=a.Disease_Code
where SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql_cz_ctg += " and a.CategoryID=@CategoryID ";
            }
            sql_cz_ctg += @"group by Group_Code,CategoryName ";
            if (dto.Type == 1)
            {
                sql_cz_ctg += " having sum(Remain)>=0) a";
            }
            if (dto.Type == -1)
            {
                sql_cz_ctg += " having sum(Remain)<0) a";
            }
            if (dto.Type == 0)
            {
                sql_cz_ctg += " ) a";
            }
            sql_cz_ctg += @"GROUP BY code
) t2 ON t1.code = t2.code AND t1.remain = t2.min_remain ";
            var cz_ctg = dapper.Select<dynamic>(sql_cz_ctg, new { dto.StartDate, dto.EndDate, dto.AreaID, dto.CategoryID });

            string sql_cz_mdc_grp = @"SELECT t1.code,t1.name,t1.remain czje
FROM (select a.CategoryID code,d.Name,sum(remain) remain from sys_list a 
inner join list_chargeinfo b on a.ListID=b.ListID
inner join sys_category c on c.CategoryID=a.CategoryID
inner join (select CategoryID,`Name` from sys_category where IsGroup=1) d on c.PID=d.CategoryID
inner join sys_dip_para e on a.Group_Code=e.Disease_Code
where SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql_cz_mdc_grp += " and a.CategoryID=@CategoryID  ";
            }
            sql_cz_mdc_grp += @"group by a.CategoryID,d.Name ";
            if (dto.Type == 1)
            {
                sql_cz_mdc_grp += " having sum(Remain)>=0) t1";
            }
            if (dto.Type == -1)
            {
                sql_cz_mdc_grp += " having sum(Remain)<0) t1";
            }
            if (dto.Type == 0)
            {
                sql_cz_mdc_grp += " ) t1";
            }
            sql_cz_mdc_grp += @"INNER JOIN (
    SELECT code, MIN(remain) as min_remain
    FROM (select a.CategoryID code,d.Name,sum(remain) remain from sys_list a 
inner join list_chargeinfo b on a.ListID=b.ListID
inner join sys_category c on c.CategoryID=a.CategoryID
inner join (select CategoryID,`Name` from sys_category where IsGroup=1) d on c.PID=d.CategoryID
inner join sys_dip_para e on a.Group_Code=e.Disease_Code
where SettleDate>=@StartDate and SettleDate<=@EndDate and a.AreaID=@AreaID ";
            if (!string.IsNullOrEmpty(dto.CategoryID))
            {
                sql_cz_mdc_grp += " and a.CategoryID=@CategoryID  ";
            }
            sql_cz_mdc_grp += @"group by a.CategoryID,d.Name ";
            if (dto.Type == 1)
            {
                sql_cz_mdc_grp += " having sum(Remain)>=0) a";
            }
            if (dto.Type == -1)
            {
                sql_cz_mdc_grp += " having sum(Remain)<0) a";
            }
            if (dto.Type == 0)
            {
                sql_cz_mdc_grp += " ) a";
            }
            sql_cz_mdc_grp += @"GROUP BY code
) t2 ON t1.code = t2.code AND t1.remain = t2.min_remain";
            var cz_mdc_grp = dapper.Select<dynamic>(sql_cz_mdc_grp, new { dto.StartDate, dto.EndDate, dto.AreaID, dto.CategoryID });

            var result = from x in list
                         join a in jc on x.code equals a.code
                         join b in jy on x.code equals b.code
                         join c in yf on x.code equals c.code
                         join d in hcf on x.code equals d.code
                         join e in cz_dc on x.code equals e.code
                         join f in cz_ctg on x.code equals f.code
                         join g in cz_mdc_grp on x.code equals g.code
                         select new DTO.Statistic.Group.OutPageList()
                         {
                             cjfy = x.cjfy,
                             jczb = Math.Round(Convert.ToDecimal(a.jcf) / x.total, 4) * 100,
                             cjjy = x.cjjy,
                             cnt = x.cnt,
                             code = x.code,
                             hzb = Math.Round(Convert.ToDecimal(d.hcf) / x.total, 4) * 100,
                             jyzb = Math.Round(Convert.ToDecimal(b.jyf) / x.total, 4) * 100,
                             name = x.name,
                             remain = x.remain,
                             total = x.total,
                             yzb = Math.Round(Convert.ToDecimal(c.yf) / x.total, 4) * 100,
                             max_dc = e.doc_name,
                             max_ctg_grp = f.name,
                             max_mdc_grp = g.name,
                             zqz = x.zqz
                         };

            return (result.ToList(), total);
        }
        public (List<DTO.Statistic.Group.OutDetailPageList> list, int total) DetailGroupPageList(DTO.Statistic.Group.InDetailPageList dto)
        {
            string sql = @"select CategoryName name,count(a.ListID) cnt,sum(Remain) remain,sum(score) zqz,sum(Total) total,round(sum(Total)/count(distinct a.ListID),2) cjfy,round(sum(Remain)/count(distinct a.ListID),2) cjjy from sys_list a 
left join list_chargeinfo b on a.ListID=b.ListID
inner join sys_dip_para c on a.Group_Code=c.Disease_Code
where SettleDate>=@StartDate and SettleDate<=@EndDate and a.Group_Code=@GroupCode and a.AreaID=@AreaID
group by CategoryName
order by @Col @Sort
limit @PageSize OFFSET @PageNo";
            var list = dapper.Select<DTO.Statistic.MedicalGroup.OutDetailPageList>(sql, new { dto.GroupCode, dto.StartDate, dto.EndDate, dto.AreaID, dto.PageSize, PageNo = (dto.PageNo - 1) * dto.PageSize, dto.Col, dto.Sort });

            string sql1 = @"select count(1) from sys_list a 
inner join sys_dip_para c on a.Group_Code=c.Disease_Code
where SettleDate>=@StartDate and SettleDate<=@EndDate and a.Group_Code=@GroupCode and a.AreaID=@AreaID";
            int total = Convert.ToInt32(dapper.ExecuteScalar(sql1, new { dto.StartDate, dto.EndDate, dto.GroupCode, dto.AreaID }));

            string sql_jc = @"select CategoryName name,sum(Amount) jcf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
where b.ItemName='检查费' and SettleDate>=@StartDate and SettleDate<=@EndDate and a.Group_Code=@GroupCode and a.AreaID=@AreaID
group by CategoryName";
            var jc = dapper.Select<dynamic>(sql_jc, new { dto.StartDate, dto.EndDate, dto.GroupCode, dto.AreaID });

            string sql_jy = @"select CategoryName name,sum(Amount) jyf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
where b.ItemName='化验费' and SettleDate>=@StartDate and SettleDate<=@EndDate and a.Group_Code=@GroupCode and a.AreaID=@AreaID
group by CategoryName";
            var jy = dapper.Select<dynamic>(sql_jy, new { dto.StartDate, dto.EndDate, dto.GroupCode, dto.AreaID });

            string sql_yf = @"select CategoryName name,sum(Amount) yf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
where b.ItemName in ('西药费','中药饮片费','中成药费') and SettleDate>=@StartDate and SettleDate<=@EndDate and a.Group_Code=@GroupCode and a.AreaID=@AreaID
group by CategoryName";
            var yf = dapper.Select<dynamic>(sql_yf, new { dto.StartDate, dto.EndDate, dto.GroupCode, dto.AreaID });

            string sql_hcf = @"select CategoryName name,sum(Amount) hcf from sys_list a 
inner join chargeinfo_item b on a.ListID=b.ListID
where b.ItemName='卫生材料费' and SettleDate>=@StartDate and SettleDate<=@EndDate and a.Group_Code=@GroupCode and a.AreaID=@AreaID
group by CategoryName";
            var hcf = dapper.Select<dynamic>(sql_hcf, new { dto.StartDate, dto.EndDate, dto.GroupCode, dto.AreaID });


            string sql_cz = @"select  name,min(remain) czje,CONCAT(DcName,' - ',DcCode) doc_name from(
select CategoryName name,sum(remain) remain,c.DcName,c.DcCode from sys_list a 
inner join list_chargeinfo b on a.ListID=b.ListID
inner join list_personinfo c on a.ListID=c.ListID
where SettleDate>=@StartDate and SettleDate<=@EndDate and a.Group_Code=@GroupCode and a.AreaID=@AreaID
group by CategoryName,c.DcName,c.DcCode ) a group by name,CONCAT(DcName,' - ',DcCode)";
            var cz = dapper.Select<dynamic>(sql_cz, new { dto.StartDate, dto.EndDate, dto.GroupCode, dto.AreaID });

            var para = ef.Set<Sys_DRG_Para>().SingleOrDefault(x => x.DRG_Code == dto.GroupCode);

            var result = from x in list
                         join a in jc on x.name equals a.name
                         join b in jy on x.name equals b.name
                         join c in yf on x.name equals c.name
                         join d in hcf on x.name equals d.name
                         join e in cz on x.name equals e.name
                         select new DTO.Statistic.Group.OutDetailPageList()
                         {
                             zqz = x.zqz,
                             cjfy = x.cjfy,
                             jczb = Math.Round(Convert.ToDecimal(a.jcf) / x.total, 4) * 100,
                             cjjy = x.cjjy,
                             cnt = x.cnt,
                             hzb = Math.Round(Convert.ToDecimal(d.hcf) / x.total, 4) * 100,
                             JMFFBZ = para.JMFFBZ,
                             jyzb = Math.Round(Convert.ToDecimal(b.jyf) / x.total, 4) * 100,
                             name = x.name,
                             remain = x.remain,
                             total = x.total,
                             yzb = Math.Round(Convert.ToDecimal(c.yf) / x.total, 4) * 100,
                             ZGFFBZ = para.ZGFFBZ,
                             czje = Convert.ToDecimal(e.czje),
                             doc_name = e.doc_name,
                         };

            return (result.ToList(), total);
        }
    }
}
